package com.it2297.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;


import com.it2297.main.App;

public class Workshop {
	
	private int workshopId;
	private String title;
	private Date dateStart;
	private Date dateEnd;
	private String description;
	private String location;
	private int capacity;
	private int availableSlot;
	private String nric;
	private String status;
	
	public Workshop(){
		
	}
	public Workshop(String title, Date dateStart, Date dateEnd, String description, String location, int capacity, int availableSlot, String nric, String status){
		this.title=title;
		this.dateStart=dateStart;
		this.dateEnd=dateEnd;
		this.description=description;
		this.location=location;
		this.capacity=capacity;
		this.availableSlot=availableSlot;
		this.nric=nric;
		this.status=status;
	}
	
	
	
	public String getStatus() {
		return status;
	}
	public void setStatus(String status) {
		this.status = status;
	}
	public String getNric() {
		return nric;
	}
	public void setNric(String nric) {
		this.nric=nric;
	}
	public int getWorkshopId() {
		return workshopId;
	}
	public void setWorkshopId(int workshopId) {
		this.workshopId = workshopId;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public Date getDateStart() {
		return dateStart;
	}
	public void setDateStart(Date dateStart) {
		this.dateStart = dateStart;
	}
	public Date getDateEnd() {
		return dateEnd;
	}
	public void setDateEnd(Date dateEnd) {
		this.dateEnd = dateEnd;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public String getLocation() {
		return location;
	}
	public void setLocation(String location) {
		this.location = location;
	}
	public int getCapacity() {
		return capacity;
	}
	public void setCapacity(int capacity) {
		this.capacity = capacity;
	}
	public int getAvailableSlot() {
		return availableSlot;
	}
	public void setAvailableSlot(int availableSlot) {
		this.availableSlot = availableSlot;
	}
private void setAttributes(ResultSet rs) {
		
	try {
			
			this.setWorkshopId(rs.getInt("workshop_id"));
			this.setTitle(rs.getString("title"));
			this.setDateStart(rs.getDate("date_start"));
			this.setDateEnd(rs.getDate("date_end"));
			this.setDescription(rs.getString("description"));
			this.setLocation(rs.getString("location"));
			this.setCapacity(rs.getInt("capacity"));
			this.setAvailableSlot(rs.getInt("available_slot"));
			this.setStatus(rs.getString("status"));
			this.setNric(rs.getString("fk_nric"));
			
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	
	}
	public static ArrayList<Workshop> getAllWorkShop(){
		ArrayList<Workshop> shop = new ArrayList<Workshop>();
		
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.workshop ";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Workshop tempshop = new Workshop();
				tempshop.setAttributes(rs);
				shop.add(tempshop);			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return shop;
	}
	
	
	
	public  Workshop getSingle(int id) {
		try {
			App.mySqlConnection.open();

			ResultSet resultSet = App.mySqlConnection.executeQuery("SELECT * FROM americano.workshop where workshop_id =" + id +" ");

			

			if (resultSet.next()) {
				this.setAttributes(resultSet);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		
		return this;
	}
	public static ArrayList<Workshop> getMultiple() {
		ArrayList<Workshop> workshops = new ArrayList<Workshop>();

		try {
			App.mySqlConnection.open();

			
			ResultSet resultSet = App.mySqlConnection.executeQuery("SELECT * FROM americano.workshop where status='ava' ");

			while (resultSet.next()) {
				Workshop workshop=new Workshop();
				workshop.setAttributes(resultSet);
				workshops.add(workshop);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return workshops;
	}
	




	public boolean createWorkshop(Workshop workshop){
		boolean success = false;
		
		int workshopId=workshop.getWorkshopId();
		String title=workshop.getTitle().toString();
		Date dateStart = workshop.getDateStart();
		Date dateEnd=workshop.getDateEnd();
		String description=workshop.getDescription().toString();
		String location= workshop.getLocation().toString();
		int capacity=workshop.getCapacity();
		int availableSlot=workshop.getAvailableSlot();
		String status=workshop.getStatus();
		String trainernric=workshop.getNric();
		SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		try{
			App.mySqlConnection.open();
			
			String sql = "INSERT INTO americano.workshop(workshop_id,title,date_start,date_end,description,location,capacity,available_slot,status,fk_nric)";
			sql+="VALUES ('" + workshopId + "','" + title + "','" + dateformat.format(dateStart) + "','" + dateformat.format(dateEnd) + "','" +description + "','" + location + "','"+capacity+"','"+availableSlot+"','"+status+"','"+trainernric+"')";
			App.mySqlConnection.executeUpdate(sql);
				success = true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
			
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	public void delWorkshop(int id){
		Workshop workshop=new Workshop();
		try {
			App.mySqlConnection.open();
			String sql = "UPDATE americano.workshop set status = ? where workshop id = ?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setString(1, "una");
			ps.setInt(2, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{	
			App.mySqlConnection.close();
		}
		
	}
	
	public static boolean updateWorkshop(Workshop workshop)
	{
		boolean success = false;
		
		int workshopId=workshop.getWorkshopId();
		String title=workshop.getTitle();
		Date dateStart = workshop.getDateStart();
		Date dateEnd=workshop.getDateEnd();
		String description=workshop.getDescription();
		String location= workshop.getLocation();
		int capacity=workshop.getCapacity();
		int availableSlot=workshop.getAvailableSlot();
		String status=workshop.getStatus();
		String nric=workshop.getNric();
		SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		try
		{
			App.mySqlConnection.open();
			
			String sql = "UPDATE americano.workshop set title='" + title + "', date_start='" + dateformat.format(dateStart) +"', date_end='"+ dateformat.format(dateEnd) +"', description='"+ description +"', location='"+ location +"', capacity='"+ capacity +"', available_slot='"+ availableSlot +"', status='" + status+"', fk_nric='"+ nric + "' WHERE workshop_id='"+ workshopId + "'";
			App.mySqlConnection.executeUpdate(sql);
			success=true;
			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}
		return success;
	}
/*
	public void updateWorkshop(Workshop workshop){
		try{
			App.mySqlConnection.open();
			String sql="UPDATE americano.workshop set title=?, date_start=?, date_end=?, description=?, location=?, capacity=?, available_slot=?, status=?, nric=?  where workshop_id=?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			ps.setString(1, workshop.getTitle());
			ps.setDate(2, dateformat.format(workshop.getDateStart()));
			ps.setDate(3,  (java.sql.Date) workshop.getDateEnd());
			ps.setString(4,  workshop.getDescription());
			ps.setString(5, workshop.getLocation());
			ps.setInt(6,  workshop.getCapacity());
			ps.setInt(7, workshop.getAvailableSlot());
			ps.setString(8, workshop.getStatus());
			ps.setString(9, workshop.getNric());
			
			
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
			
		}
		
		
		*/
		
		
	
	public static boolean IsWorkshopIdExists(int id){
		boolean result = false;
		ResultSet rs = null;
		String sql = "SELECT * FROM americano.workshop WHERE workshop_id='" + id + "' and status = 'ava'";
		
		try {
			App.mySqlConnection.open();
			rs = App.mySqlConnection.executeQuery(sql);
			if (rs.next())
				result = true;
			else
				result = false;
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return result;
	}
	public  boolean delWorkshop(Workshop workshop) {
		boolean success = false;
        String status="nva";
		int id = workshop.getWorkshopId();
		
		try {
			App.mySqlConnection.open();
			String sql = "UPDATE americano.workshop SET status = '" + status + "'";
			sql += "WHERE workshop_id = '" +  id +"'";
			
			App.mySqlConnection.executeUpdate(sql);
			success = true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	/*
	public static ArrayList<Workshop> workshopSearch(Workshop workshop){
		ArrayList<Workshop> workshopsearch = new ArrayList<Workshop>();
		ResultSet rs = null;
		
		String title = workshop.getTitle().toString();
		
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.workshop WHERE title LIKE '"  + '%' + title + '%' + "' and status = 'ava'";
			rs=App.mySqlConnection.executeQuery(sql);
			
			
			if(!rs.next()){
				workshop.setTitle("");
			}
			else{
				rs.beforeFirst();
				
					while(rs.next()){	
						Workshop workshops=new Workshop();
						workshops.setTitle(rs.getString("title"));
						workshops.setDateStart(rs.getDate("date_start"));
						workshops.setDateEnd(rs.getDate("date_end"));
						workshops.setDescription(rs.getString("description"));
						workshops.setLocation(rs.getString("location"));
						workshops.setAvailableSlot(rs.getInt("available_slot"));
						workshops.setNric(rs.getString("fk_nric"));
						workshopsearch.add(workshops);
					}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return workshopsearch;
	}
	*/
	public  Workshop getSingleSearch(String title) {
		try {
			App.mySqlConnection.open();

			ResultSet resultSet = App.mySqlConnection.executeQuery("SELECT * FROM americano.workshop WHERE title LIKE '"  + '%' + title + '%' + "' and status = 'ava'");

			

			if (resultSet.next()) {
				this.setAttributes(resultSet);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		
		return this;
	}
	public static int getWorkshopRecordNumber() {
		int recordNum = 0;

		try {
			App.mySqlConnection.open();
			String sql = "SELECT COUNT(*) FROM americano.workshop";
			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next()) {
				recordNum = resultSet.getInt(1);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return recordNum;
	}
	public static ArrayList<Workshop> getMultipleWorkshops(int index, int count) {
		ArrayList<Workshop> manyws = new ArrayList<Workshop>();
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.workshop where status = 'ava' LIMIT ?, ?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setInt(1, index);
			ps.setInt(2, count);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Workshop workshop=new Workshop();
				workshop.setAttributes(rs);
				manyws.add(workshop);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return manyws;
	}
	
	
	
	
}